﻿using Groep2TINGHService.Get_Set_Classes;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Groep2TINGHService.DB_Classes
{
    public class LocationDB
    {
        //Geeft een lijst terug met alle Locations
        public static List<Location> GetAllLocations()
        {
            List<Location> lijst = new List<Location>();
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("select Name from Location", connection);
            try
            {
                Location temp;
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();
                int Name = reader.GetOrdinal("Name");

                while (reader.Read())
                {
                    temp = new Location();
                    temp.Name = reader.GetString(Name);
                    lijst.Add(temp);

                }
                reader.Close();
                return lijst;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Geeft het id terug van een Location
        public static Guid GetIdfromLocation(string locationName)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("SELECT Id from Location WHERE Name=@Name", connection);
            selectcommand.Parameters.AddWithValue("@Name", locationName);
            try
            {
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();

                Location temp = new Location();

                if (reader.Read())
                {
                    int Id = reader.GetOrdinal("Id");
                    temp.Id = reader.GetGuid(Id);
                }
                reader.Close();

                Guid IdLocation = temp.Id;
                return IdLocation;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        //Geeft de naam terug van een Location aan de hand van ReservationId
        public static string GetNamefromLocation(string reservationId)
        {
            SqlConnection connection = ConnectionDB.GetConnection();
            SqlCommand selectcommand = new SqlCommand("SELECT l.Name from Location l, Reservation r WHERE r.Id = @Id and l.Id = r.LocationId", connection);
            selectcommand.Parameters.AddWithValue("@Id", reservationId);
            try
            {
                connection.Open();
                SqlDataReader reader = selectcommand.ExecuteReader();

                Location temp = new Location();

                if (reader.Read())
                {
                    int Name = reader.GetOrdinal("Name");
                    temp.Name = reader.GetString(Name);
                }
                reader.Close();

                string NameLocation = temp.Name;
                return NameLocation;
            }
            catch (SqlException ex)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}